using MultiAgentsShared;
using System.Reflection;
using System.Text.Json;

namespace MultiAgentsServer.Scripts.Database;

public class DatabaseRepository(DataBaseConnectService dbService)
{
    public enum DataBaseTableNames
    {
        AgentInstances,
        AgentTemplates,
        ChatRooms,
        Configurations,
        Files,
        ManagerUsers,
        Messages,
        Projects,
        SystematicNotifications,
        Users,
        UserSubscriptions,
        WorkflowConfigs,
    }

    private readonly DataBaseConnectService dbService = dbService;

    public int AddRow<TDatabaseClass>(string tableName, TDatabaseClass item) where TDatabaseClass : DatabaseTable
    {
        FieldInfo[] infos = item?.GetType().GetFields(BindingFlags.Instance | BindingFlags.NonPublic) ?? throw Debug.ShouldNotHappen();
        string sqlFirst = @$"INSERT INTO {tableName} (";
        string sqlSecond = @$"VALUES (";
        Dictionary<string, object> parameters = [];

        for (int i = 0; i < infos.Length; i++)
        {
            string name = infos[i].Name;
            
            object? value = infos[i].GetValue(item);

            if (name == "_id" || name ==  "dbException" || value == null)
            {
                continue;
            }

            name = char.ToUpper(name[1]) + name[2..];
            sqlFirst += name + ", ";

            sqlSecond += "@" + name + ", ";

            if (infos[i].FieldType.IsGenericType && infos[i].FieldType.GetGenericTypeDefinition() == typeof(List<>))
            {
                parameters.Add("@" + name, JsonSerializer.Serialize(value));
            }
            else if (infos[i].FieldType.IsGenericType && infos[i].FieldType.GetGenericTypeDefinition() == typeof(Dictionary<,>))
            {
                parameters.Add("@" + name, JsonSerializer.Serialize(value));
            }
            else
            {
                parameters.Add("@" + name, value);
            }
        }

        sqlFirst = sqlFirst[..^2] + ") ";
        sqlSecond = sqlSecond[..^2] + "); SELECT LAST_INSERT_ID();";

        object? lastInsertId = dbService.ExecuteScalar(sqlFirst + sqlSecond, parameters);
        return Convert.ToInt32(lastInsertId);
    }

    public bool DeleteRow<TDatabaseClass>(string tableName, TDatabaseClass item) where TDatabaseClass : DatabaseTable
    {
        FieldInfo[] infos = item?.GetType().GetFields(BindingFlags.Instance | BindingFlags.NonPublic) ?? throw Debug.ShouldNotHappen();
        string sql = @$"DELETE FROM {tableName} WHERE ";

        Dictionary<string, object> parameters = [];

        for (int i = 0; i < infos.Length; i++)
        {
            string name = infos[i].Name;
            object? value = infos[i].GetValue(item);

            if (name == "dbException" || value == null)
            {
                continue;
            }

            name = char.ToUpper(name[1]) + name[2..];

            sql += $"{name} = @{name} AND ";

            if (infos[i].FieldType.IsGenericType && infos[i].FieldType.GetGenericTypeDefinition() == typeof(List<>))
            {
                parameters.Add("@" + name, JsonSerializer.Serialize(value));
            }
            else if (infos[i].FieldType.IsGenericType && infos[i].FieldType.GetGenericTypeDefinition() == typeof(Dictionary<,>))
            {
                parameters.Add("@" + name, JsonSerializer.Serialize(value));
            }
            else
            {
                parameters.Add("@" + name, value);
            }
        }

        sql = sql[..^5];

        int affectedRows = dbService.ExecuteNonQuery(sql, parameters);
        return affectedRows > 0;
    }

    public List<TDatabaseClass> QueryRows<TDatabaseClass>(string tableName, TDatabaseClass item) where TDatabaseClass : DatabaseTable, new()
    {
        FieldInfo[] infos = item?.GetType().GetFields(BindingFlags.Instance | BindingFlags.NonPublic) ?? throw Debug.ShouldNotHappen();
        string sql = @$"SELECT * FROM {tableName} WHERE ";

        Dictionary<string, object> parameters = [];

        for (int i = 0; i < infos.Length; i++)
        {
            string name = infos[i].Name;
            object? value = infos[i].GetValue(item);

            if (name == "dbException" || value == null)
            {
                continue;
            }

            name = char.ToUpper(name[1]) + name[2..];

            sql += $"{name} = @{name} AND ";

            if (infos[i].FieldType.IsGenericType && infos[i].FieldType.GetGenericTypeDefinition() == typeof(List<>))
            {
                parameters.Add("@" + name, JsonSerializer.Serialize(value));
            }
            else if (infos[i].FieldType.IsGenericType && infos[i].FieldType.GetGenericTypeDefinition() == typeof(Dictionary<,>))
            {
                parameters.Add("@" + name, JsonSerializer.Serialize(value));
            }
            else
            {
                parameters.Add("@" + name, value);
            }
        }

        if (parameters.Count == 0)
        {
            sql = sql[..^7];
        }
        else
        {
            sql = sql[..^5];
        }
        

        List<Dictionary<string, object>> results = dbService.ExecuteQuery(sql, parameters);
        List<TDatabaseClass> resultDBs = [];
        foreach (Dictionary<string, object> row in results)
        {
            TDatabaseClass resultDB = Activator.CreateInstance(typeof(TDatabaseClass), [row]) as TDatabaseClass ?? throw Debug.ShouldNotHappen();
            resultDBs.Add(resultDB);
        }

        return resultDBs;
    }

    public bool UpdateRowById<TDatabaseClass>(string tableName, TDatabaseClass item) where TDatabaseClass : DatabaseTable, new()
    {
        FieldInfo[] infos = item?.GetType().GetFields(BindingFlags.Instance | BindingFlags.NonPublic) ?? throw Debug.ShouldNotHappen();
        string sql = @$"UPDATE {tableName} SET ";

        Dictionary<string, object> parameters = [];

        for (int i = 0; i < infos.Length; i++)
        {
            string name = infos[i].Name;
            object? value = infos[i].GetValue(item);

            if (name == "dbException" || value == null)
            {
                continue;
            }
            else if (name == "_id")
            {
                parameters.Add("@Id", value);
                continue;
            }

            name = char.ToUpper(name[1]) + name[2..];

            sql += $"{name} = @{name}, ";

            if (infos[i].FieldType.IsGenericType && infos[i].FieldType.GetGenericTypeDefinition() == typeof(List<>))
            {
                parameters.Add("@" + name, JsonSerializer.Serialize(value));
            }
            else if (infos[i].FieldType.IsGenericType && infos[i].FieldType.GetGenericTypeDefinition() == typeof(Dictionary<,>))
            {
                parameters.Add("@" + name, JsonSerializer.Serialize(value));
            }
            else
            {
                parameters.Add("@" + name, value);
            }
        }

        if (parameters.Count == 0)
        {
            throw Debug.ShouldNotHappen();
        }
        else
        {
            sql = sql[..^2];
        }

        sql += " WHERE Id = @Id";

        int affectedRows = dbService.ExecuteNonQuery(sql, parameters);
        return affectedRows > 0;
    }
}
